package dao;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Meetstation;
import model.Meting;

public class MeetstationDAO {

	private PreparedStatement prst;
	private String qStationID;
	private String qStationNaam;
	private String qUpdateStation;
	private String qCreateStation;
	private String qDeleteStation;
	private String qAlleStationNamen;
	private String qMetingenByID;
	private String qMetingenByDate;
	private String qDeleteMeting;
	private ResultSet rs = null;
	private Connection conn = null;

	public MeetstationDAO() {
		qStationID = "SELECT * FROM station WHERE station_id=?";
		qStationNaam = "SELECT * FROM station WHERE plaats=?";
		qUpdateStation = "UPDATE station SET station_id= ?, plaats= ?, "
				+ "positie= ?, terreinHoogte= ?, omgeving= ?, grondsoort= ?, baroHoogte=?, windHoogte=?"
				+ "WHERE station_id=?";
		qCreateStation = "INSERT INTO station VALUES (?,?,?,?,?,?,?,?)";
		qAlleStationNamen = "SELECT plaats FROM station";
		qDeleteStation = "DELETE FROM station WHERE station_id=?";
		qMetingenByID = "SELECT * FROM metingen WHERE station_id=?";
		qDeleteMeting = "DELETE FROM metingen WHERE station_id=? AND datum=?";
		qMetingenByDate = "SELECT * FROM metingen WHERE station_id=? AND datum BETWEEN ? AND ?";
	}

	public Meetstation getStationByID(int station_id) throws SQLException {
		Meetstation meetstation = null;

		conn = DBManager.getInstance().getConnection();

		try {

			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qStationID);
			prst.setInt(1, station_id);
			rs = prst.executeQuery();
			rs.first();
			meetstation = new Meetstation(rs.getInt(1), rs.getString(2), rs
					.getString(3), rs.getDouble(4), rs.getString(5), rs
					.getString(6), rs.getDouble(7), rs.getDouble(8));

			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
		return meetstation;
	}

	public Meetstation getStationByName(String station_naam) throws SQLException {
		Meetstation meetstation = null;

		conn = DBManager.getInstance().getConnection();

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qStationNaam);
			prst.setString(1, station_naam);
			rs = prst.executeQuery();

			conn.commit();

			rs.first();

			meetstation = new Meetstation(rs.getInt(1), rs.getString(2), rs
					.getString(3), rs.getDouble(4), rs.getString(5), rs
					.getString(6), rs.getDouble(7), rs.getDouble(8));

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
		return meetstation;
	}

	public void updateMeetstation(Meetstation station) throws SQLException {

		conn = DBManager.getInstance().getConnection();

		try {

			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qUpdateStation);
			prst.setInt(1, station.getStationID());
			prst.setString(2, station.getPlaats());
			prst.setString(3, station.getPositie());
			prst.setDouble(4, station.getTerreinHoogte());
			prst.setString(5, station.getOmgeving());
			prst.setString(6, station.getGrondsoort());
			prst.setDouble(7, station.getBaroHoogte());
			prst.setDouble(8, station.getWindHoogte());
			prst.setInt(9, station.getStationID());
			prst.executeUpdate();

			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
	}

	public void createMeetstation(Meetstation station) throws SQLException {

		conn = DBManager.getInstance().getConnection();

		try {

			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qCreateStation);
			prst.setInt(1, station.getStationID());
			prst.setString(2, station.getPlaats());
			prst.setString(3, station.getPositie());
			prst.setDouble(4, station.getTerreinHoogte());
			prst.setString(5, station.getOmgeving());
			prst.setString(6, station.getGrondsoort());
			prst.setDouble(7, station.getBaroHoogte());
			prst.setDouble(8, station.getWindHoogte());
			prst.execute();
			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
	}

	public void deleteMeetstation(int station_id) throws SQLException {

		conn = DBManager.getInstance().getConnection();

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qDeleteStation);
			prst.setInt(1, station_id);
			prst.execute();

			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
	}

	public ArrayList<String> getAlleStationNamen() throws SQLException {
		ArrayList<String> stations = new ArrayList<String>();

		conn = DBManager.getInstance().getConnection();
		ResultSet rs;

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qAlleStationNamen);
			rs = prst.executeQuery();

			conn.commit();

			while (rs.next()) {
				stations.add(rs.getString(1));
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}

		return stations;
	}

	public ArrayList<Meting> getMetingen(int station_id2) throws SQLException {
		ArrayList<Meting> metingen = new ArrayList<Meting>();
		Meting meting = null;

		conn = DBManager.getInstance().getConnection();
		ResultSet rs;

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qMetingenByID);
			prst.setInt(1, station_id2);

			rs = prst.executeQuery();

			conn.commit();

			while (rs.next()) {

				int station_id = rs.getInt(1);
				Date datum = rs.getDate(2);
				int windrichting = rs.getInt(3);
				int gemWindsnelheid = rs.getInt(4);
				int maxWindsnelheid = rs.getInt(5);
				int minWindsnelheid = rs.getInt(6);
				int gemTemperatuur = rs.getInt(7);
				int maxTemperatuur = rs.getInt(8);
				int minTemperatuur = rs.getInt(9);
				int minTemperatuur10 = rs.getInt(10);
				int gemLuchtdruk = rs.getInt(11);
				int maxLuchtdruk = rs.getInt(12);
				int minLuchtdruk = rs.getInt(13);
				int duurNeerslag = rs.getInt(14);
				int somNeerslag = rs.getInt(15);
				meting = new Meting(station_id, datum, windrichting,
						gemWindsnelheid, maxWindsnelheid, minWindsnelheid,
						gemTemperatuur, maxTemperatuur, minTemperatuur,
						minTemperatuur10, gemLuchtdruk, maxLuchtdruk,
						minLuchtdruk, duurNeerslag, somNeerslag);
				metingen.add(meting);
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}

		return metingen;
	}
	
	public void deleteMeting( int id, Date datum ) throws SQLException {
		
		conn = DBManager.getInstance().getConnection();
		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qDeleteMeting);
			prst.setInt(1, id);
			prst.setDate(2, datum);
			prst.execute();

			conn.commit();

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}
	}
	
	public ArrayList<Meting> getMetingen(int station_id2, java.sql.Date beginDatum, java.sql.Date eindDatum ) throws SQLException {
		ArrayList<Meting> metingen = new ArrayList<Meting>();
		Meting meting = null;

		conn = DBManager.getInstance().getConnection();
		ResultSet rs;

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(qMetingenByDate);
			prst.setInt(1, station_id2);
			prst.setDate(2, beginDatum);
			prst.setDate(3, eindDatum);
			
			rs = prst.executeQuery();

			conn.commit();

			while (rs.next()) {

				int station_id = rs.getInt(1);
				Date datum = rs.getDate(2);
				int windrichting = rs.getInt(3);
				int gemWindsnelheid = rs.getInt(4);
				int maxWindsnelheid = rs.getInt(5);
				int minWindsnelheid = rs.getInt(6);
				int gemTemperatuur = rs.getInt(7);
				int maxTemperatuur = rs.getInt(8);
				int minTemperatuur = rs.getInt(9);
				int minTemperatuur10 = rs.getInt(10);
				int gemLuchtdruk = rs.getInt(11);
				int maxLuchtdruk = rs.getInt(12);
				int minLuchtdruk = rs.getInt(13);
				int duurNeerslag = rs.getInt(14);
				int somNeerslag = rs.getInt(15);
				meting = new Meting(station_id, datum, windrichting,
						gemWindsnelheid, maxWindsnelheid, minWindsnelheid,
						gemTemperatuur, maxTemperatuur, minTemperatuur,
						minTemperatuur10, gemLuchtdruk, maxLuchtdruk,
						minLuchtdruk, duurNeerslag, somNeerslag);
				metingen.add(meting);
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				throw e1;
			}
			e.printStackTrace();
			throw e;
		} finally {
			DBManager.getInstance().close();
		}

		return metingen;
	}
	
	
}
